package com.loiot.baqi.utils;

import java.io.File;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map.Entry;

import com.loiot.baqi.pojo.Columns;
import com.loiot.baqi.pojo.Tables;
import com.loiot.commons.utils.DateUtil;
import com.loiot.commons.utils.FileUtil;
import com.loiot.commons.utils.JsonUtil;
import com.timeloit.pojo.ReflectUtil;
 
/**
 * 此类为工具类，用于实现虚拟ORM。根据给定的model类生成
 * 数据库操作语句。
 * @author lw
 */
public class VirtualORM {
	
	
	  /**
     * 保存一个对象的sql语句。最后两个参数是临时为止，
     * 他们的存在令人很不爽。
     * @param bean
     * @param sql 用于带回sql语句的参数
     * @param params 用于带回参数的参数
     * @return 
     * @throws Exception 
     */
    public static DBParams save(Object bean) throws Exception{
        Class<? extends Object> beanClass = bean.getClass();
        List<String> fields   = new ArrayList<String>();
        List<Method> getters  = new ArrayList<Method>();
        getGetters(beanClass, fields, getters);
         
        /**开始拼装sql和params*/
        List<Object> tempParams = new ArrayList<Object>();
        String insertSql = "insert into " + covertToTableName(beanClass);
        String valueSql  = "";
        String columnSql = "";
        Method getter    = null;
        String field     = null;
        for(int i=0;i<fields.size();i++){
            getter = getters.get(i);
            field  =  fields.get(i);
            if(getMethodValue(getter,bean)!=null){
            	 columnSql += (field+",");
                 valueSql += getMethodValue(getter,bean)+",";
            }
            try{
                tempParams.add(getter.invoke(bean, null));
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        columnSql = ( "(" + toColumn(columnSql) + ")").replace(",)", ")");
        valueSql = ( "(" + valueSql + ")").replace(",)", ")");
         
        DBParams DBP = new DBParams();
        DBP.setSql(insertSql+columnSql+"values"+valueSql);
        DBP.setParams(tempParams.toArray());
        System.out.println(DBP.getSql());
        return DBP;
    }
    
    
    
    /**
     * 更新一条记录
     * @param bean 所要更新的表
     * @param columns 所要查询的字段
     * @param whereSql 查询的条件
     * @return 
     * @throws Exception 
     */
    public static DBParams update(Object bean,String[] columns,String whereSql) throws Exception{
        Class<? extends Object> beanClass = bean.getClass();
        List<String> fields  = new ArrayList<String>();
        List<Method> getters = new ArrayList<Method>();
        getGetters(beanClass, fields, getters);
         
        /**过滤出要更新的字段*/
        int oldSize = fields.size();
        int index = -1;
        for(int i=0;i<columns.length;i++){
            index = fields.indexOf(columns[i].toString().toLowerCase());
            if(index != -1){
                fields.add(fields.get(index));
                getters.add(getters.get(index));
            }
        }
        int newSize = fields.size();
        fields = fields.subList(oldSize,newSize);
        getters = getters.subList(oldSize, newSize);
         
        /**开始拼装sql和params*/
        List<Object> tempParams = new ArrayList<Object>();
        String updateSql = "update " + beanClass.getSimpleName() + " set ";
        String columnSql = "";
        Method getter    = null;
        String field     = null;
        for(int i=0;i<fields.size();i++){
            getter = getters.get(i);
            field  =  fields.get(i);
            columnSql += ("," + field+"=?");
            try{
                tempParams.add(getter.invoke(bean, null));
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        columnSql = columnSql.replaceFirst(",", "");
        /**返回*/
        DBParams DBP = new DBParams();
        DBP.setSql(updateSql+columnSql+" WHERE"+whereSql);
        DBP.setParams(tempParams.toArray());
        return DBP;
    }
    
    /**
     * @param bean 要持久化得对象
     * @param breakNull 持久化时是否忽略值为null的属性
     * @param whereSql 查询条件
     * @return 
     * @throws Exception 
     */
    public static DBParams update(Object bean,boolean breakNull,String whereSql) throws Exception{
        Class<? extends Object> beanClass = bean.getClass();
        List<String> fields  = new LinkedList<String>();
        List<Method> getters = new ArrayList<Method>();
        getGetters(beanClass, fields, getters);
         
        List<Object> tempParams = new LinkedList<Object>();
        Method getter = null;
        for(int i=0;i<getters.size();i++){
            getter = getters.get(i);
            try {
                tempParams.add(getMethodValue(getter, bean));
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        
        /**排除null属性，不予保存*/
        if(breakNull){
            List<Object> tempParams2 = new LinkedList<Object>();
            List<String> fields2  = new LinkedList<String>();

            for(int i=0;i<tempParams.size();i++){
                if(tempParams.get(i)==null){
                }else {
                	fields2.add(fields.get(i));
                	tempParams2.add(tempParams.get(i));
                }
            }
            tempParams.clear();
            tempParams.addAll(tempParams2);
            fields.clear();
            fields.addAll(fields2);
        }
        /**开始拼接sql语句*/
        String updateSql = "update " + covertToTableName(beanClass) + " set ";
        String columnSql = "";
        for(int i=0;i<tempParams.size();i++){
            columnSql += ("," + fields.get(i)+"=" +tempParams.get(i) );
        }
         
        columnSql = columnSql.replaceFirst(",", "");
        /**返回*/
        DBParams DBP = new DBParams();
        DBP.setSql(updateSql+columnSql+" "+whereSql);
        DBP.setParams(tempParams.toArray());
        return DBP;
    }
    
    
    /**
     * @param bean 要持久化得对象
     * @param breakNull 持久化时是否忽略值为null的属性
     * @param whereSql 查询条件
     * @return 
     * @throws Exception 
     */
    public static DBParams update(Object bean,boolean breakNull) throws Exception{
    	Class<? extends Object> beanClass = bean.getClass();
        List<String> fields  = new LinkedList<String>();
        List<Method> getters = new ArrayList<Method>();
        getGetters(beanClass, fields, getters);
         
        Tables tableInfo = getTableInfoByCache(beanClass);
    	
        List<Object> tempParams = new LinkedList<Object>();
        Method getter = null;
        for(int i=0;i<getters.size();i++){
            getter = getters.get(i);
            try {
                tempParams.add(getMethodValue(getter, bean));
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        
        /**排除null属性，不予保存*/
        if(breakNull){
            List<Object> tempParams2 = new LinkedList<Object>();
            List<String> fields2  = new LinkedList<String>();

            for(int i=0;i<tempParams.size();i++){
                if(tempParams.get(i)==null){
                }else {
                	fields2.add(fields.get(i));
                	tempParams2.add(tempParams.get(i));
                }
            }
            tempParams.clear();
            tempParams.addAll(tempParams2);
            fields.clear();
            fields.addAll(fields2);
        }
        /**开始拼接sql语句*/
        String updateSql = "update " + covertToTableName(beanClass) + " set ";
        String columnSql = "";
        for(int i=0;i<tempParams.size();i++){
            columnSql += ("," + fields.get(i)+"=" +tempParams.get(i) );
        }
         
        columnSql = columnSql.replaceFirst(",", "");
        
        
        /**返回*/
        DBParams DBP = new DBParams();
        DBP.setSql(updateSql+columnSql+" WHERE "+tableInfo.getPkName() +"="+ ReflectUtil.getFieldValue(bean, convertToJavaProperty(tableInfo.getPkName())));
        DBP.setParams(tempParams.toArray());
        return DBP;
    }
    /**
     * 拼接出一个select sql 语句
     * @param table 所要查询的表
     * @param columns 所要查询的字段
     * @param whereSql 查询的条件
     * @return 
     */
    public static String select(String table,String[] columns,String whereSql){
        /**开始拼装sql和params*/
        String selectSql = "select ? from " + table + " ";
        String columnSql = "";
        for(int i=0;i<columns.length;i++){
            columnSql += (","+columns[i]);
        }
        columnSql = columnSql.replaceFirst(",", "");
        selectSql = selectSql.replace("?", columnSql);
        return selectSql+" "+whereSql;
    }
    /**
     * 找到指定类的所有getters()方法，但是去除Object父类的getClass()方法。
     * @param beanClass 指定的持久化对象
     * @param fields 返回所有写有getters的方法的属性，Object父类的class除外   
     * @param getters 返回所有除了从Object继承的getters方法
     * @throws Exception 
     */
    private static void getGetters(Class<? extends Object> beanClass,List<String> fields,List<Method> getters) throws Exception{
      
    
   	  	Tables tableInfo = getTableInfoByCache(beanClass);
    	
    	Method[] methods = beanClass.getMethods();
  	  	HashMap<String,Method> map = new HashMap<String,Method>();
        String field = null,methodName = null;
        for(Method meth:methods){
            methodName  = meth.getName();
            field       = methodName.substring(3).toLowerCase();
             
            if(methodName.substring(0, 3).equals("get")){
            	field=methodName.replace("get", "");
            	field=field.substring(0, 1).toLowerCase()+field.substring(1);
            	field=toColumn(field);
            	if(exitsColumn(field,tableInfo)){
                	map.put(field,meth);
            	}
            }
        }
        map.remove("class");
        map.entrySet();
        for(Entry<String,Method> item:map.entrySet()){
            fields.add(item.getKey());
            getters.add(item.getValue());
        }
    }
    
    public static String toColumn(String word) {  
        StringBuffer sb = new StringBuffer();  
        for (int i = 0; i < word.length(); i++) {  
            char c = word.charAt(i);  
            if (Character.isUpperCase(c)) {  
                sb.append("_"+c);  
            }else{  
                sb.append(c);  
            }  
        }  
        return sb.toString().toLowerCase();  
    }  
    
    public static  Tables getTableInfoByCache(Class<? extends Object> beanClass) throws Exception {
  	  //String tablesJson = FileUtil.readFileToString(new File("d:\\tables.txt"));
  	  //List<Tables> tables = JsonUtil.toList(tablesJson, Tables.class);
      List<Tables> tables = IndexInfoSingleTon.getInstance().getTables();
      String tableName=covertToTableName(beanClass);
  	  for(Tables table: tables){
  		if(table.getTableName().equalsIgnoreCase(tableName)){
  			return table;
  		}
  	  }
  	  return null;
    }
    
    //判断列是否存在
    public static boolean  exitsColumn(String columnName,Tables tableInfo){
    	for(Columns column:tableInfo.getColumns()){
    		if(column.getColumnName().equalsIgnoreCase(columnName)){
    			return true;
    		}
    	}
    	return false;
    }
    
    public static String covertToTableName(Class<? extends Object> beanClass){
    	  String className=beanClass.getSimpleName();
     	  String tableName=className.substring(0, 1).toLowerCase()+className.substring(1);
     	  tableName=toColumn(tableName);
    	return tableName;
    }
    
    public static String convertToJavaProperty(String columnName){
    	  StringBuffer buff = new StringBuffer();
          for(int i=0;i<columnName.length();i++){
        	  char c=columnName.charAt(i);
        	  boolean b =false;
        	  if(buff.length()>0){
        		  char c2=columnName.charAt(i-1);
            	  if(c2=='_'){
            		 b=true;
            		 buff.append(String.valueOf(c).toUpperCase());
            	  }
        	  }
        	  if(!b){
            	  buff.append(c);
            	  b=false;
        	  }
        	
        	 // System.out.println(c);
          } 
    	  		
	  	return buff.toString().replaceAll("_", "");
    }
    
    public static Object getMethodValue(Method getter,Object bean) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException{
    	Object obj =getter.invoke(bean, null);
    	if(obj!=null && obj instanceof String && obj.toString().length()>0){
    		obj="'"+ obj+"'";
    	}else
    	if(obj!=null && obj instanceof Date){
    		obj="'"+DateUtil.toString((Date)obj)+"'";
    	}
    	return obj;
    }
    
    
    public static DBParams deleteCacheByPk(Class classsz,Long id) throws Exception{
    	Tables tableInfo = getTableInfoByCache(classsz);
        String SQL = "DELETE FROM " + tableInfo.getTableName() +"  WHERE "+tableInfo.getPkName() +"="+id;
    	DBParams DBP = new DBParams();
        DBP.setSql(SQL);
        //DBP.setParams(null);
        System.out.println(DBP.getSql());
        return DBP;
    }
    
    public static DBParams deleteByCache(Object bean) throws Exception{
    	boolean breakNull=true;
    	Class<? extends Object> beanClass = bean.getClass();
        List<String> fields  = new LinkedList<String>();
        List<Method> getters = new ArrayList<Method>();
        getGetters(beanClass, fields, getters);
         
        Tables tableInfo = getTableInfoByCache(beanClass);
    	
        List<Object> tempParams = new LinkedList<Object>();
        Method getter = null;
        for(int i=0;i<getters.size();i++){
            getter = getters.get(i);
            try {
                tempParams.add(getMethodValue(getter, bean));
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        
        /**排除null属性，不予保存*/
        if(breakNull){
            List<Object> tempParams2 = new LinkedList<Object>();
            List<String> fields2  = new LinkedList<String>();

            for(int i=0;i<tempParams.size();i++){
                if(tempParams.get(i)==null){
                }else {
                	fields2.add(fields.get(i));
                	tempParams2.add(tempParams.get(i));
                }
            }
            tempParams.clear();
            tempParams.addAll(tempParams2);
            fields.clear();
            fields.addAll(fields2);
        }
        /**开始拼接sql语句*/
        String updateSql = "DELETE FROM  " + tableInfo.getTableName() + " WHERE 1=1 " ;
        String columnSql = "";
        for(int i=0;i<tempParams.size();i++){
            columnSql += (" AND " + fields.get(i)+"=" +tempParams.get(i) );
        }
         
        columnSql = columnSql.replaceFirst(",", "");
        
        /**返回*/
        DBParams DBP = new DBParams();
        DBP.setSql(updateSql+columnSql);
        DBP.setParams(tempParams.toArray());
        System.out.println(DBP.getSql());
        return DBP;
    }
    
    
    public static DBParams getObjectById(Class bean,Long id) throws Exception{
        Tables tableInfo = getTableInfoByCache(bean);
        /**开始拼装sql和params*/
        List<Object> tempParams = new ArrayList<Object>();
        String columnSql = "";
        Method getter    = null;
        String field     = null;
        for(int i=0;i<tableInfo.getColumns().size();i++){
        	Columns columnObj = tableInfo.getColumns().get(i);
            columnSql += ("," + columnObj.getColumnName());
            try{
                //tempParams.add(getter.invoke(bean, null));
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        columnSql = columnSql.replaceFirst(",", "");
        String sql ="SELECT  "+columnSql+" FROM " + tableInfo.getTableName() + "  WHERE "+tableInfo.getPkName() +"="+id;
        /**返回*/
        DBParams DBP = new DBParams();
        DBP.setSql(sql);
        DBP.setParams(tempParams.toArray());
        System.out.println("sql:"+sql);
        return DBP;
    }
    
    public static DBParams getObject(Object bean) throws Exception{
        Class<? extends Object> beanClass = bean.getClass();
        Tables tableInfo = getTableInfoByCache(beanClass);
        List<String> fields  = new LinkedList<String>();
        List<Method> getters = new ArrayList<Method>();
        getGetters(beanClass, fields, getters);
        /**开始拼装sql和params*/
        String columnSql = "";
     
        for(int i=0;i<tableInfo.getColumns().size();i++){
        	Columns columnObj = tableInfo.getColumns().get(i);
            columnSql += ("," + columnObj.getColumnName());
            try{
                //tempParams.add(getter.invoke(bean, null));
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        List<Object> tempParams = new ArrayList<Object>();
        Method getter    = null;
        String field     = null;
        for(int i=0;i<getters.size();i++){
            getter = getters.get(i);
            try {
                tempParams.add(getMethodValue(getter, bean));
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        String whereSql = "";
        for(int i=0;i<tempParams.size();i++){
        	if(tempParams.get(i)!=null)
        	  whereSql += (" AND " + fields.get(i)+"=" +tempParams.get(i) );
        }
        //whereSql = whereSql.replace("  ", "");
        columnSql = columnSql.replaceFirst(",", "");
        String sql ="SELECT  "+columnSql+" FROM " + tableInfo.getTableName() + "  WHERE 1=1 "+ whereSql;
        /**返回*/
        DBParams DBP = new DBParams();
        DBP.setSql(sql);
        DBP.setParams(tempParams.toArray());
        System.out.println("sql:"+sql);
        return DBP;
    }
}